package com.ndp.fb.rdb.mongo.impl;

import com.alibaba.dubbo.config.annotation.Service;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.mongodb.*;
import com.ndp.fb.constant.MongoConstants;
import com.ndp.fb.model.business.Sort;
import com.ndp.fb.model.business.TimeRange;
import com.ndp.fb.model.business.vo.admanage.param.GroupByAdSetParam;
import com.ndp.fb.model.business.vo.admanage.param.GroupByDayParam;
import com.ndp.fb.mongo.api.AdGroupMongoService;
import com.ndp.fb.mongo.model.AdForMongo;
import com.ndp.fb.mongo.model.AdSetForMongo;
import com.ndp.fb.mongo.model.FBAdForMongo;
import com.ndp.fb.mongo.model.ImageVersion;
import com.ndp.fb.mongo.model.Serializer.PropertyFilter;
import com.ndp.fb.rdb.model.AdGroup;
import com.ndp.fb.util.DateUtil;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;

import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * Created by jacky.cheng on 2015/6/18.
 */
@Service
public class AdGroupMongoServiceImpl implements AdGroupMongoService {
    private final Logger logger = LoggerFactory.getLogger(getClass());
    @Autowired
    private DB mongoDBClient;

    @Override
    public void save(AdGroup ad) {
        List<AdGroup> list = new ArrayList<>();
        list.add(ad);
        saveAll(list);
    }

    private static Map<String, String> GA_DATA_FIELD_NAME_MAP = new HashMap() {{
        put("gaClicks", "gaSessions");                          // GA点击
        put("gaCart", "gaGoal1Completions");                   // GA加入购物车
        put("gaPays", "gaTransactions");                        // GA购买
        put("gaOrderAmount", "gaTransactionRevenue");        // GA销售额
    }};

    @Override
    public void saveAll(List<AdGroup> ads) {

        if (ads == null || ads.size() < 1) {
            return;
        }
        DBCollection dbCollection = mongoDBClient.getCollection(MongoConstants.TABLE_NAME_DELETED_ADGROUP);
        List<DBObject> data = new ArrayList<>();
        for (AdGroup record : ads) {
            String jsonString = JSONObject.toJSONString(record);
            DBObject o = (DBObject) com.mongodb.util.JSON.parse(jsonString);
            data.add(o);
        }
        dbCollection.insert(data);
    }

    @Override
    public void upsetAdForMongos(List<FBAdForMongo> adForMongos) {
        if (CollectionUtils.isNotEmpty(adForMongos)) {
            Long start = System.currentTimeMillis();
            int size = adForMongos.size();
            DBCollection dbCollection = mongoDBClient.getCollection(MongoConstants.TABLE_NAME_ADGROUP);
            DBCollection dbStatsCollection = mongoDBClient.getCollection(MongoConstants.TABLE_NAME_ADGROUP_STATS_BY_DAY);
            List<DBObject> tmpList = (List<DBObject>) com.mongodb.util.JSON
                    .parse(JSONObject.toJSONString(adForMongos, new PropertyFilter()));
            DBObject updateData;
            DBObject updateStatsData;
            DBObject queryStatsData;
            Map adset = null;
            Map promoted_object = null;
            for (DBObject dbObject : tmpList) {
                Date dateUpdateTime = new Date();
                updateStatsData = new BasicDBObject();
                updateStatsData.put("mongodbId", dbObject.get("ad_id") + "_" + "1970-01-01");
                updateStatsData.put("adGroupId", dbObject.get("ad_id"));
                updateStatsData.put("campaignGroupId", dbObject.get("campaign_id"));
                updateStatsData.put("campaignId", dbObject.get("adset_id"));
                adset = (Map) dbObject.get("adSet");
                promoted_object = adset == null ? null : (Map) adset.get("promoted_object");
                updateStatsData.put("fbConnectionObjectId",
                        promoted_object == null ? 0L : promoted_object.get("application_id"));
                updateStatsData.put("platform", dbObject.get("platform"));
                updateStatsData.put("accountId", dbObject.get("account_id"));
                updateStatsData.put("createTime", dbObject.get("created_utc_time"));
                updateStatsData.put("day", "1970-01-01");
                updateStatsData.put("dataUpdateTime", dateUpdateTime);
                queryStatsData = new BasicDBObject("mongodbId", dbObject.get("ad_id") + "_" + "1970-01-01");
                dbStatsCollection.update(queryStatsData, new BasicDBObject("$set", updateStatsData), true, false);
                updateData = new BasicDBObject();
                updateData.put("$set", dbObject);
                dbObject.put("dataUpdateTime", dateUpdateTime);
                dbCollection.update(new BasicDBObject("ad_id", dbObject.get("ad_id")), updateData, true, false);
            }
            logger.debug("本次总共更新AdForMongo数据{}条,花费时间为{}", size, System.currentTimeMillis() - start);
        }
    }

    @Override
    public AdForMongo findAdForMongo(Long ad_id) {
        DBCollection dbCollection = mongoDBClient.getCollection(MongoConstants.TABLE_NAME_ADGROUP);
        DBObject dbObject = dbCollection.findOne(new BasicDBObject("ad_id", ad_id));
        if (dbObject == null) {
            return null;
        }
        return JSONObject.parseObject(JSONObject.toJSONString(dbObject), AdForMongo.class);
    }

    @Override
    public List<AdForMongo> findAdForMongoHashIsNotInit(int pageNum, int limit) {
        DBCollection dbCollection = mongoDBClient.getCollection(MongoConstants.TABLE_NAME_ADGROUP);
        DBObject dbObject = new BasicDBObject("image_init", null);
        dbObject.put("account_id", new BasicDBObject("$gt", 0));
        DBCursor dbCursor = dbCollection.find(dbObject).limit(500).skip((pageNum - 1) * 500);
        if (dbCursor == null) {
            return null;
        }
        List<AdForMongo> adForMongos = new ArrayList<>();
        while (dbCursor.hasNext()) {
            adForMongos.add(JSONObject.parseObject(JSONObject.toJSONString(dbCursor.next()), AdForMongo.class));
        }
        return adForMongos;
    }

    @Override
    public void updateAdForMongo(Long ad_id, Map<String, Object> map) {
        Long start = System.currentTimeMillis();
        DBCollection dbCollection = mongoDBClient.getCollection(MongoConstants.TABLE_NAME_ADGROUP);
        DBObject update = new BasicDBObject();
        update.putAll(map);
        DBObject updateData = new BasicDBObject();
        updateData.put("$set", update);
        Date dateUpdateTime = new Date();
        update.put("dataUpdateTime", dateUpdateTime);
        dbCollection.update(new BasicDBObject("ad_id", ad_id), updateData, true, false);
        logger.info("更新广告成功,广告对象的mongoid：{},花费时间为{}", ad_id, System.currentTimeMillis() - start);
    }

    @Override
    public List<AdForMongo> findAdForMongoHashIsNull(int skip, int limit) {
        DBCollection dbCollection = mongoDBClient.getCollection(MongoConstants.TABLE_NAME_ADGROUP);
        DBObject o = new BasicDBObject();
        o.put("imageInfo.imageList.hash", "0");
        o.put("imageInfo.imageList.md5", new BasicDBObject("$ne", null));
        DBCursor dbCursor = dbCollection.find(o).limit(limit).skip(skip);
        if (dbCursor == null) {
            return null;
        }
        List<AdForMongo> adForMongos = new ArrayList<>();
        while (dbCursor.hasNext()) {
            adForMongos.add(JSONObject.parseObject(JSONObject.toJSONString(dbCursor.next()), AdForMongo.class));
        }
        return adForMongos;
    }

    @Override
    public void updateAdForMongoHashByMd5(Long ad_id, int version, int i, String hash) {
        Long start = System.currentTimeMillis();
        DBCollection dbCollection = mongoDBClient.getCollection(MongoConstants.TABLE_NAME_ADGROUP);
        DBObject o = new BasicDBObject();
        o.put("ad_id", ad_id);
        o.put("imageInfo.version", version);
        dbCollection.update(o,
                new BasicDBObject("$set", new BasicDBObject("imageInfo.$.imageList." + i + ".hash", hash)));
        logger.info("更新广告的hash成功,广告对象的mongoid：{},花费时间为{}", ad_id, System.currentTimeMillis() - start);
    }

    @Override
    public void updateAdForMongoCreatorInfo(Long ad_id, Long actor_id, String actor_name, Long application_id,
                                            String application_name) {
        Long start = System.currentTimeMillis();
        DBCollection dbStatsCollection = mongoDBClient.getCollection(MongoConstants.TABLE_NAME_ADGROUP_STATS_BY_DAY);
        DBObject updateStatsData = new BasicDBObject();
        updateStatsData.put("mongodbId", ad_id + "_" + "1970-01-01");
        updateStatsData.put("actor_id", actor_id);
        updateStatsData.put("actor_name", actor_name);
        updateStatsData.put("application_id", application_id);
        updateStatsData.put("application_name", application_name);
        DBObject queryStatsData = new BasicDBObject("mongodbId", ad_id + "_" + "1970-01-01");
        dbStatsCollection.update(queryStatsData, new BasicDBObject("$set", updateStatsData), true, false);
        DBCollection dbCollection = mongoDBClient.getCollection(MongoConstants.TABLE_NAME_ADGROUP);
        DBObject q = new BasicDBObject();
        q.put("ad_id", ad_id);
        DBObject o = new BasicDBObject();
        o.put("actor_id", actor_id);
        o.put("actor_name", actor_name);
        o.put("application_id", application_id);
        o.put("application_name", application_name);
        dbCollection.update(q, new BasicDBObject("$set", o), true, false);
        logger.info("更新广告的创建者信息成功,广告对象的mongoid：{},花费时间为{}", ad_id, System.currentTimeMillis() - start);
    }

    @Override
    public void addNewImageVersion(Long ad_id, ImageVersion imageVersion) {
        Long start = System.currentTimeMillis();
        DBCollection dbCollection = mongoDBClient.getCollection(MongoConstants.TABLE_NAME_ADGROUP);
        DBObject q = new BasicDBObject();
        q.put("ad_id", ad_id);
        DBObject o = new BasicDBObject();
        DBObject object = (DBObject) com.mongodb.util.JSON.parse(JSONObject.toJSONString(imageVersion));
        o.put("$each", new Object[]{object});
        o.put("$position", 0);
        WriteResult result = dbCollection.update(q, new BasicDBObject("$push", new BasicDBObject("imageInfo", o)));
        logger.info("更新广告的图片版本信息成功,广告对象的mongoid：{},花费时间为{}", ad_id, System.currentTimeMillis() - start);
    }

    @Override
    public Long getAccountAdCount(Long account_id, Long created_time) {
        List<DBObject> pipeline = new ArrayList<>();
        DBObject match = new BasicDBObject("account_id", account_id);
        match.put("created_utc_time", new BasicDBObject("$lt", created_time));
        pipeline.add(new BasicDBObject("$match", match));
        DBObject group = new BasicDBObject("_id", "$account_id");
        group.put("total", new BasicDBObject("$sum", 1));
        pipeline.add(new BasicDBObject("$group", group));

        long start = System.currentTimeMillis();
        AggregationOptions aggregationOptions = AggregationOptions.builder().allowDiskUse(true).build();
        Cursor cursor = mongoDBClient.getCollection(MongoConstants.TABLE_NAME_ADGROUP).aggregate(pipeline, aggregationOptions);
        logger.info("Mongo聚合查询花费时间：" + (System.currentTimeMillis() - start) + "毫秒");
        try {
            if (cursor.hasNext()) {
                DBObject dbObject = cursor.next();
                if (dbObject != null && dbObject.get("total") != null) {
                    return Long.valueOf(dbObject.get("total").toString());
                }
            }
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        } finally {
            cursor.close();
        }
        return 0L;
    }

    @Override
    public void saveAccountAdSnapShot(Long account_id, Date create_time, int count) {
        DBCollection dbCollection = mongoDBClient.getCollection(MongoConstants.TABLE_NAME_ACCOUNT_ADS);
        DBObject o = new BasicDBObject();
        o.put("account_id", account_id);
        o.put("created_time", create_time);
        o.put("created_utc_time", create_time.getTime());
        o.put("count", count);
        dbCollection.insert(o);
    }

    @Override
    public Long getMaxCreateUtcTime(Long account_id) {
        DBCollection dbCollection = mongoDBClient.getCollection(MongoConstants.TABLE_NAME_ACCOUNT_ADS);
        DBObject o = new BasicDBObject();
        o.put("account_id", account_id);
        Cursor cursor = dbCollection.find(o).sort(new BasicDBObject("created_utc_time", -1)).limit(1);
        try {
            if (cursor.hasNext()) {
                DBObject dbObject = cursor.next();
                if (dbObject != null && dbObject.get("created_utc_time") != null) {
                    return Long.valueOf(dbObject.get("created_utc_time").toString());
                }
            }
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        } finally {
            cursor.close();
        }
        return 0L;
    }

    @Override
    public List<AdForMongo> findAdGroupAfterCreatedUtcTime(long accountId, long createdUtcTime, String[] returnFields) {

        DBCollection dbCollection = mongoDBClient.getCollection(MongoConstants.TABLE_NAME_ADGROUP);
        DBObject match = new BasicDBObject()
                .append("account_id", accountId)
                .append("created_utc_time", new BasicDBObject("$gt", createdUtcTime));
        DBObject project = new BasicDBObject();
        for (String returnField : returnFields) {
            project.put(returnField, 1);
        }
        List<AdForMongo> ads = new ArrayList<>();
        DBCursor dbCursor = null;
        try {
            dbCursor = dbCollection.find(match, project);
            if (dbCursor == null) {
                return null;
            }
            while (dbCursor.hasNext()) {
                ads.add(JSONObject.parseObject(JSONObject.toJSONString(dbCursor.next()), AdForMongo.class));
            }
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        } finally {
            if (null != dbCursor) {
                dbCursor.close();
            }
        }
        return ads;
    }

    /**
     * @param groupByAdSetParam     "fbConnectionObjectId": 118316091858310,  // 应用id，即产品id（必须项）
     *                              "platform": "google_play"                   // 应用平台（可选项，如果未选择平台，该参数可以不传）
     *                              "accountIds": [
     *                                  825708010838893,
     *                                  826214027476674,
     *                                  851599978271412,
     *                                  851600424938034
     *                              ],                                          // 账号列表（必须项，至少选择一个账号）
     *                              "adName" : ["A","B"],                     // 广告名称筛选条件（可选项，必须以数组形式传递，即使只有一个，不支持以下符号 (*.?+$^[](){}|/) ，请在前端予以过滤，只要包含这些字符，提示出错 ）
     *                              "adSetName" : ["A","B"],                  // 广告组名称筛选条件（同adName）
     *                              "campaignName" : ["A","B"],                   // campaign名称筛选条件（同adName）
     *                              "adSetId" : 12345678,                     // 广告组编号筛选条件（可选项）
     *                              "adSetStatus" : "ACTIVE",                   // 广告组状态筛选条件（必须项，可选范围： ACTIVE, PAUSED, DELETED, PENDING_REVIEW, DISAPPROVED, PREAPPROVED, PENDING_BILLING_INFO, CAMPAIGN_PAUSED, ARCHIVED, ADSET_PAUSED ）
     *                              "createTimeRange": {
     *                                  "since": "2016-01-19 00:00:00",
     *                                  "until": "2016-01-21 00:00:00"
     *                              },                                          // 创建时间筛选条件（必须项，请根据当前登陆用户时区将选择的时间转化为UTC时间即零时区时间，精确到秒，格式请参考示例）
     *                              "statsTimeRange": {
     *                                  "since": "2016-01-19",
     *                                  "until": "2016-01-21"
     *                              },                                          // 统计时间筛选条件（必须项，不需要转换时区，精确到天，格式请参考示例）
     *                              "pSize": 20,                              // 分页大小（必须项，正整数）
     *                              "cPage": 1,                                   // 请求第几页（必须项，从1开始）
     *                              "sort" : {
     *                                  "clicks" : -1
     *                              },                                          // 排序参数，其中clicks代表排序字段，取值范围请参考统计数据字段一览，-1代表排序类型（1代表升序，-1代表降序）（必须项）
     *                              "returnStatsFields" : ["clicks","ctr","cr"] // 统计数据返回字段，为了避免不必要的网络消耗，对于需要返回的统计数据通过这里指定，取值范围请参考统计数据字段一览（必须项）
     * @return
     */
    @Override
    public Map<String, Object> groupByAdset(GroupByAdSetParam groupByAdSetParam) {
        // match，根据页面传过来的参数过滤数据
        BasicDBObject matchChildObject1 = new BasicDBObject()
                .append("account_id", new BasicDBObject("$in", groupByAdSetParam.getAccountIds()));           // 账号过滤
        if (null == groupByAdSetParam.getEcReport() || !groupByAdSetParam.getEcReport()) { // EC报表不能根据应用进行查询
            matchChildObject1.put("adSet.promoted_object.application_id", groupByAdSetParam.getFbConnectionObjectId());
        }

        String adSetStatus = groupByAdSetParam.getAdSetStatus();
        if (StringUtils.isNotEmpty(adSetStatus)) {
            matchChildObject1.put("adSet.effective_status", adSetStatus);
        }
        // 广告创建时间过滤
        TimeRange createTimeRange = groupByAdSetParam.getCreateTimeRange();
        String createTimeRangeSince = createTimeRange.getSince();
        String createTimeRangeUntil = createTimeRange.getUntil();
        matchChildObject1.put("created_utc_time",
                new BasicDBObject().
                        append("$gte", DateUtil.stringtoDate(createTimeRangeSince, "yyyy-MM-dd HH:mm:ss").getTime()).
                        append("$lte", DateUtil.stringtoDate(createTimeRangeUntil, "yyyy-MM-dd HH:mm:ss").getTime())
        );

        // 应用平台过滤
        String platform = groupByAdSetParam.getPlatform();
        if (StringUtils.isNotEmpty(platform)) {
            matchChildObject1.put("platform", platform);
        }

        // 广告名称过滤
        List<BasicDBObject> adNameRegexChildList = createRegexChildList("name", groupByAdSetParam.getAdName());
        if (CollectionUtils.isNotEmpty(adNameRegexChildList)) {
            matchChildObject1.put("$and", adNameRegexChildList);
        }

        // 广告组名称过滤
        List<BasicDBObject> adSetNameRegexChildList = createRegexChildList("adSet.name", groupByAdSetParam.getAdSetName());
        if (CollectionUtils.isNotEmpty(adSetNameRegexChildList)) {
            matchChildObject1.put("$and", adSetNameRegexChildList);
        }

        // campaign名称过滤
        List<BasicDBObject> campaignNameRegexChildList = createRegexChildList("campaign.name", groupByAdSetParam.getCampaignName());
        if (CollectionUtils.isNotEmpty(campaignNameRegexChildList)) {
            matchChildObject1.put("$and", campaignNameRegexChildList);
        }

        // 广告编号过滤
        String adId = groupByAdSetParam.getAdId();
        if (StringUtils.isNotEmpty(adId)) {
            matchChildObject1.put("ad_id", adId);
            if (NumberUtils.isNumber(adId)) {
                matchChildObject1.put("ad_id", Long.valueOf(adId));
            }
        }

        // 广告组编号过滤
        String adSetId = groupByAdSetParam.getAdSetId();
        if (StringUtils.isNotEmpty(adSetId)) {
            matchChildObject1.put("adset_id", adSetId);
            if (NumberUtils.isNumber(adSetId)) {
                matchChildObject1.put("adset_id", Long.valueOf(adSetId));
            }
        }

        // 广告系列编号过滤
        String campaignId = groupByAdSetParam.getCampaignId();
        if (StringUtils.isNotEmpty(campaignId)) {
            matchChildObject1.put("campaign_id", campaignId);
            if (NumberUtils.isNumber(campaignId)) {
                matchChildObject1.put("campaign_id", Long.valueOf(campaignId));
            }
        }

        // group，根据adSetId分组，以便减少Lookup的数据量
        BasicDBObject groupChildObject1 = new BasicDBObject()
                .append("_id", "$adset_id")                                                     // 分组条件
                .append("accountId", new BasicDBObject("$first", "$account_id"))                // 广告账号Id
                .append("adSetId", new BasicDBObject("$first", "$adset_id"))                    // 广告组编号
                .append("adSetName", new BasicDBObject("$first", "$adSet.name"))                // 广告组名称
                .append("adSetStatus", new BasicDBObject("$first", "$adSet.effective_status"))  // 广告组状态
                .append("adSetCreatedUtcTime", new BasicDBObject("$first", "$adSet.created_utc_time"))  // 广告组创建时间
                .append("adSetConfiguredStatus", new BasicDBObject("$first", "$adSet.configured_status"))  // 广告组状态
                .append("campaignName", new BasicDBObject("$first", "$campaign.name"))          // campaign名称
                .append("bidAmount", new BasicDBObject("$first", "$adSet.bid_amount"))          // 出价
                .append("budget", new BasicDBObject("$first",                                   // 预算
                        new BasicDBObject("$cond",
                                new BasicDBObject()
                                        .append("if", new BasicDBObject("$eq", new String[]{"$adSet.daily_budget", "0"}))
                                        .append("then", "$adSet.lifetime_budget")
                                        .append("else", "$adSet.daily_budget"))))
                .append("ads", new BasicDBObject("$push",
                        new BasicDBObject()
                                .append("adId", "$ad_id")                                       // 广告编号
                                .append("adName", "$name")                                      // 广告名称
                                .append("adStatus", "$effective_status")
                                .append("adCreatedUtcTime", "$created_utc_time")  // 广告创建时间
                                .append("adConfiguredStatus", "$configured_status")));                     // 广告状态

        // lookup，拼接统计数据
        BasicDBObject lookupChildObject = createLookupProject(MongoConstants.TABLE_NAME_ADGROUP_STATS_BY_DAY_90DAYS, "adSetId", "campaignId", "statsData");

        // unwind，将之前通过adSet聚合到一起的广告分割开
        String unwindChild1Object1 = "$ads";

        // project，过滤统计数据，只保留统计周期和今日的数据
        String statsTimeRangeSince = groupByAdSetParam.getStatsTimeRange().getSince();
        String statsTimeRangeUntil = groupByAdSetParam.getStatsTimeRange().getUntil();
        String statsToday = groupByAdSetParam.getToday();
        BasicDBObject projectChildObject1 = new BasicDBObject()
                .append("adId", "$ads.adId")
                .append("adStatus", "$ads.adStatus")
                .append("adCreatedUtcTime", "$ads.adCreatedUtcTime")
                .append("adConfiguredStatus", "$ads.adConfiguredStatus")
                .append("adName", "$ads.adName")
                .append("accountId", 1)
                .append("adSetId", 1)
                .append("adSetStatus", 1)
                .append("adSetCreatedUtcTime", 1)
                .append("adSetConfiguredStatus", 1)
                .append("adSetName", 1)
                .append("bidAmount", 1)
                .append("budget", 1)
                .append("campaignName", 1)
                .append("statsData", new BasicDBObject("$filter",
                        new BasicDBObject()
                                .append("input", "$statsData")
                                .append("as", "statsData")
                                .append("cond", new BasicDBObject()
                                        .append("$and", new BasicDBObject[]{
                                                new BasicDBObject("$eq", new String[]{"$ads.adId", "$$statsData.adGroupId"}),
                                                new BasicDBObject("$or", new BasicDBObject[]{
                                                        new BasicDBObject("$and", new BasicDBObject[]{
                                                                new BasicDBObject("$gte", new String[]{"$$statsData.day", statsTimeRangeSince}),
                                                                new BasicDBObject("$lte", new String[]{"$$statsData.day", statsTimeRangeUntil}),
                                                        }),
                                                        new BasicDBObject("$eq", new String[]{"$$statsData.day", statsToday})
                                                })
                                        }))));

        // unwind，将统计数据分割开来，然后再进行SUM等计算
        BasicDBObject unwindChild1Object2 = new BasicDBObject()
                .append("path", "$statsData")
                .append("preserveNullAndEmptyArrays", true);

        // group，根据ad分组，计算ad的统计数据
        BasicDBObject groupChildObject2 = new BasicDBObject()
                .append("_id", "$adId")
                .append("adId", new BasicDBObject("$first", "$adId"))
                .append("accountId", new BasicDBObject("$first", "$accountId"))
                .append("adStatus", new BasicDBObject("$first", "$adStatus"))
                .append("adConfiguredStatus", new BasicDBObject("$first", "$adConfiguredStatus"))
                .append("adCreatedUtcTime", new BasicDBObject("$first", "$adCreatedUtcTime"))
                .append("adName", new BasicDBObject("$first", "$adName"))
                .append("adSetId", new BasicDBObject("$first", "$adSetId"))
                .append("adSetStatus", new BasicDBObject("$first", "$adSetStatus"))
                .append("adSetCreatedUtcTime", new BasicDBObject("$first", "$adSetCreatedUtcTime"))  // 广告组创建时间
                .append("adSetConfiguredStatus", new BasicDBObject("$first", "$adSetConfiguredStatus"))
                .append("adSetName", new BasicDBObject("$first", "$adSetName"))
                .append("bidAmount", new BasicDBObject("$first", "$bidAmount"))
                .append("budget", new BasicDBObject("$first", "$budget"))
                .append("campaignName", new BasicDBObject("$first", "$campaignName"))
                .append("todaySpent", createSumObjectByTimeRange("spent", "$statsData.", statsToday, statsToday));  // 今日花费
        List<String> returnStatsFields = groupByAdSetParam.getReturnStatsFields();

        // group，根据adSet分组，计算adSet的统计数据，并且将ad的数据放到一个数组里
        BasicDBObject groupChildObject3 = new BasicDBObject()
                .append("_id", "$adSetId")
                .append("id", new BasicDBObject("$first", "$adSetId"))
                .append("name", new BasicDBObject("$first", "$adSetName"))
                .append("status", new BasicDBObject("$first", "$adSetStatus"))
                .append("createdUtcTime", new BasicDBObject("$first", "$adSetCreatedUtcTime"))
                .append("configuredStatus", new BasicDBObject("$first", "$adSetConfiguredStatus"))
                .append("bidAmount", new BasicDBObject("$first", "$bidAmount"))
                .append("budget", new BasicDBObject("$first", "$budget"))
                .append("campaignName", new BasicDBObject("$first", "$campaignName"))
                .append("todaySpent", new BasicDBObject("$sum", "$todaySpent"))        // 今日花费
                .append("adCount", new BasicDBObject("$sum", 1));
        BasicDBObject adsObject = new BasicDBObject()
                .append("id", "$adId")
                .append("name", "$adName")
                .append("adSetName", "$adSetName")
                .append("campaignName", "$campaignName")
                .append("status", "$adStatus")
                .append("createdUtcTime", "$adCreatedUtcTime")
                .append("configuredStatus", "$adConfiguredStatus")
                .append("todaySpent", "$todaySpent");
        groupChildObject3.put("ads", new BasicDBObject("$push", adsObject));

        // project，计算adSet的ctr,cr,cost等
        BasicDBObject projectChildObject2 = new BasicDBObject()
                .append("adCount", "$adCount")
                .append("ads", "$ads")
                .append("id", "$id")
                .append("name", "$name")
                .append("status", "$status")
                .append("createdUtcTime", "$createdUtcTime")
                .append("configuredStatus", "$configuredStatus")
                .append("bidAmount", "$bidAmount")
                .append("budget", "$budget")
                .append("campaignName", "$campaignName")
                .append("todaySpent", "$todaySpent");

        // sort，根据指定的排序条件进行排序，另外为了避免每次查询得到的数据不一样，第二排序因子默认采用asSetId
        Sort sort = groupByAdSetParam.getSort();
        BasicDBObject sortChildObject1 = new BasicDBObject()
                .append(sort.getField(), sort.getType())
                .append("id", 1);

        // group，将所有数据汇总到一起，计算统计行和总条数等
        BasicDBObject groupChildObject4 = new BasicDBObject()
                .append("_id", null)
                .append("adCount", new BasicDBObject("$sum", "$adCount"))                      // 广告数量
                .append("adSetCount", new BasicDBObject("$sum", 1))                            // 广告组数量
                .append("todaySpent", new BasicDBObject("$sum", "$todaySpent"))               // 今日花费
                .append("adSets", new BasicDBObject("$push", "$$CURRENT"));                   // adSet数组

        // project，分页，计算ctr,cr,cost等
        Integer currentPage = groupByAdSetParam.getCPage();
        Integer pageSize = groupByAdSetParam.getPSize();
        int startNum = (currentPage - 1) * pageSize;
        BasicDBObject projectChildObject3 = new BasicDBObject()
                .append("adSets", new BasicDBObject("$slice", new Object[]{"$adSets", startNum, pageSize}));
        BasicDBObject summaryObject = new BasicDBObject()
                .append("adCount", "$adCount")
                .append("adSetCount", "$adSetCount")
                .append("todaySpent", "$todaySpent");
        projectChildObject3.put("summary", summaryObject);

        // 对于需要返回的统计字段，进行统一处理
        String resultsField = "results";
        String resultsMapField = "results";
        if (null != groupByAdSetParam.getEcReport() && groupByAdSetParam.getEcReport()) {  // EC报表时转化应该对应purchase
            resultsField = "purchase";
            resultsMapField = "convertedArray.offsite_conversion.fb_pixel_purchase.actions.1d_click";
        }
        for (String returnField : returnStatsFields) {
            switch (returnField) {
                case "ctr":
                    groupChildObject2.put("impressions", createSumObjectByTimeRange("impressions", "$statsData.", statsTimeRangeSince, statsTimeRangeUntil));
                    groupChildObject2.put("clicks", createSumObjectByTimeRange("clicks", "$statsData.", statsTimeRangeSince, statsTimeRangeUntil));
                    groupChildObject3.put("impressions", new BasicDBObject("$sum", "$impressions"));
                    groupChildObject3.put("clicks", new BasicDBObject("$sum", "$clicks"));
                    projectChildObject2.put("impressions", "$impressions");
                    projectChildObject2.put("clicks", "$clicks");
                    groupChildObject4.put("impressions", new BasicDBObject("$sum", "$impressions"));
                    groupChildObject4.put("clicks", new BasicDBObject("$sum", "$clicks"));
                    adsObject.put("ctr", createDivideObject("$clicks", "$impressions", 0L));
                    projectChildObject2.put("ctr", createDivideObject("$clicks", "$impressions", 0L));
                    summaryObject.put("ctr", createDivideObject("$clicks", "$impressions", 0L));
                    break;
                case "cr":
                    groupChildObject2.put("clicks", createSumObjectByTimeRange("clicks", "$statsData.", statsTimeRangeSince, statsTimeRangeUntil));
                    groupChildObject2.put(resultsField, createSumObjectByTimeRange(resultsMapField, "$statsData.", statsTimeRangeSince, statsTimeRangeUntil));
                    groupChildObject3.put("clicks", new BasicDBObject("$sum", "$clicks"));
                    groupChildObject3.put(resultsField, new BasicDBObject("$sum", "$" + resultsField));
                    projectChildObject2.put("clicks", "$clicks");
                    projectChildObject2.put(resultsField, "$" + resultsField);
                    groupChildObject4.put("clicks", new BasicDBObject("$sum", "$clicks"));
                    groupChildObject4.put(resultsField, new BasicDBObject("$sum", "$" + resultsField));
                    adsObject.put("cr", createDivideObject("$" + resultsField, "$clicks", 0L));
                    projectChildObject2.put("cr", createDivideObject("$" + resultsField, "$clicks", 0L));
                    summaryObject.put("cr", createDivideObject("$" + resultsField, "$clicks", 0L));
                    break;
                case "cpc":
                    groupChildObject2.put("spent", createSumObjectByTimeRange("spent", "$statsData.", statsTimeRangeSince, statsTimeRangeUntil));
                    groupChildObject2.put("clicks", createSumObjectByTimeRange("clicks", "$statsData.", statsTimeRangeSince, statsTimeRangeUntil));
                    groupChildObject3.put("spent", new BasicDBObject("$sum", "$spent"));
                    groupChildObject3.put("clicks", new BasicDBObject("$sum", "$clicks"));
                    projectChildObject2.put("spent", "$spent");
                    projectChildObject2.put("clicks", "$clicks");
                    groupChildObject4.put("spent", new BasicDBObject("$sum", "$spent"));
                    groupChildObject4.put("clicks", new BasicDBObject("$sum", "$clicks"));
                    adsObject.put("cpc", createDivideObject("$spent", "$clicks", "$spent"));
                    projectChildObject2.put("cpc", createDivideObject("$spent", "$clicks", "$spent"));
                    summaryObject.put("cpc", createDivideObject("$spent", "$clicks", "$spent"));
                    break;
                case "cost":
                case "cps":
                    groupChildObject2.put("spent", createSumObjectByTimeRange("spent", "$statsData.", statsTimeRangeSince, statsTimeRangeUntil));
                    groupChildObject2.put(resultsField, createSumObjectByTimeRange(resultsMapField, "$statsData.", statsTimeRangeSince, statsTimeRangeUntil));
                    groupChildObject3.put("spent", new BasicDBObject("$sum", "$spent"));
                    groupChildObject3.put(resultsField, new BasicDBObject("$sum", "$" + resultsField));
                    projectChildObject2.put("spent", "$spent");
                    projectChildObject2.put(resultsField, "$" + resultsField);
                    groupChildObject4.put("spent", new BasicDBObject("$sum", "$spent"));
                    groupChildObject4.put(resultsField, new BasicDBObject("$sum", "$" + resultsField));
                    adsObject.put(returnField, createDivideObject("$spent", "$" + resultsField, "$spent"));
                    projectChildObject2.put(returnField, createDivideObject("$spent", "$" + resultsField, "$spent"));
                    summaryObject.put(returnField, createDivideObject("$spent", "$" + resultsField, "$spent"));
                    break;
                case "cartR":
                    groupChildObject2.put("cart", createSumObjectByTimeRange("convertedArray.offsite_conversion.fb_pixel_add_to_cart.actions.1d_click", "$statsData.", statsTimeRangeSince, statsTimeRangeUntil));
                    groupChildObject2.put("clicks", createSumObjectByTimeRange("clicks", "$statsData.", statsTimeRangeSince, statsTimeRangeUntil));
                    groupChildObject3.put("cart", new BasicDBObject("$sum", "$cart"));
                    groupChildObject3.put("clicks", new BasicDBObject("$sum", "$clicks"));
                    projectChildObject2.put("cart", "$cart");
                    projectChildObject2.put("clicks", "$clicks");
                    groupChildObject4.put("cart", new BasicDBObject("$sum", "$cart"));
                    groupChildObject4.put("clicks", new BasicDBObject("$sum", "$clicks"));
                    adsObject.put("cartR", createDivideObject("$cart", "$clicks", 0L));
                    projectChildObject2.put("cartR", createDivideObject("$cart", "$clicks", 0L));
                    summaryObject.put("cartR", createDivideObject("$cart", "$clicks", 0L));
                    break;
                case "purchaseR":
                    groupChildObject2.put("purchase", createSumObjectByTimeRange("convertedArray.offsite_conversion.fb_pixel_purchase.actions.1d_click", "$statsData.", statsTimeRangeSince, statsTimeRangeUntil));
                    groupChildObject2.put("cart", createSumObjectByTimeRange("convertedArray.offsite_conversion.fb_pixel_add_to_cart.actions.1d_click", "$statsData.", statsTimeRangeSince, statsTimeRangeUntil));
                    groupChildObject3.put("purchase", new BasicDBObject("$sum", "$purchase"));
                    groupChildObject3.put("cart", new BasicDBObject("$sum", "$cart"));
                    projectChildObject2.put("purchase", "$purchase");
                    projectChildObject2.put("cart", "$cart");
                    groupChildObject4.put("purchase", new BasicDBObject("$sum", "$purchase"));
                    groupChildObject4.put("cart", new BasicDBObject("$sum", "$cart"));
                    adsObject.put("purchaseR", createDivideObject("$purchase", "$cart", 0L));
                    projectChildObject2.put("purchaseR", createDivideObject("$purchase", "$cart", 0L));
                    summaryObject.put("purchaseR", createDivideObject("$purchase", "$cart", 0L));
                    break;
                case "roi":
                    groupChildObject2.put("salesAmount", createSumObjectByTimeRange("convertedArray.offsite_conversion.fb_pixel_purchase.actionValues.1d_click", "$statsData.", statsTimeRangeSince, statsTimeRangeUntil));
                    groupChildObject2.put("spent", createSumObjectByTimeRange("spent", "$statsData.", statsTimeRangeSince, statsTimeRangeUntil));
                    groupChildObject3.put("salesAmount", new BasicDBObject("$sum", "$salesAmount"));
                    groupChildObject3.put("spent", new BasicDBObject("$sum", "$spent"));
                    projectChildObject2.put("salesAmount", "$salesAmount");
                    projectChildObject2.put("spent", "$spent");
                    groupChildObject4.put("salesAmount", new BasicDBObject("$sum", "$salesAmount"));
                    groupChildObject4.put("spent", new BasicDBObject("$sum", "$spent"));
                    adsObject.put("roi", createDivideObject("$salesAmount", "$spent", 0L));
                    projectChildObject2.put("roi", createDivideObject("$salesAmount", "$spent", 0L));
                    summaryObject.put("roi", createDivideObject("$salesAmount", "$spent", 0L));
                    break;
                case "retr":
                    adsObject.put("retr", new BasicDBObject("$literal", "-"));
                    projectChildObject2.put("retr", new BasicDBObject("$literal", "-"));
                    summaryObject.put("retr", new BasicDBObject("$literal", "-"));
                    break;
                case "cart":
                    groupChildObject2.put(returnField, createSumObjectByTimeRange("convertedArray.offsite_conversion.fb_pixel_add_to_cart.actions.1d_click", "$statsData.", statsTimeRangeSince, statsTimeRangeUntil));
                    groupChildObject3.put(returnField, new BasicDBObject("$sum", "$" + returnField));
                    adsObject.put(returnField, "$" + returnField);
                    groupChildObject4.put(returnField, new BasicDBObject("$sum", "$" + returnField));
                    projectChildObject2.put(returnField, "$" + returnField);
                    summaryObject.put(returnField, "$" + returnField);
                    break;
                case "purchase":
                    groupChildObject2.put(returnField, createSumObjectByTimeRange("convertedArray.offsite_conversion.fb_pixel_purchase.actions.1d_click", "$statsData.", statsTimeRangeSince, statsTimeRangeUntil));
                    groupChildObject3.put(returnField, new BasicDBObject("$sum", "$" + returnField));
                    adsObject.put(returnField, "$" + returnField);
                    groupChildObject4.put(returnField, new BasicDBObject("$sum", "$" + returnField));
                    projectChildObject2.put(returnField, "$" + returnField);
                    summaryObject.put(returnField, "$" + returnField);
                    break;
                case "salesAmount":
                    groupChildObject2.put(returnField, createSumObjectByTimeRange("convertedArray.offsite_conversion.fb_pixel_purchase.actionValues.1d_click", "$statsData.", statsTimeRangeSince, statsTimeRangeUntil));
                    groupChildObject3.put(returnField, new BasicDBObject("$sum", "$" + returnField));
                    adsObject.put(returnField, "$" + returnField);
                    groupChildObject4.put(returnField, new BasicDBObject("$sum", "$" + returnField));
                    projectChildObject2.put(returnField, "$" + returnField);
                    summaryObject.put(returnField, "$" + returnField);
                    break;
                case "gaClicks":
                case "gaCart":
                case "gaPays":
                case "gaOrderAmount":
                    groupChildObject2.put(returnField, createSumObjectByTimeRange(GA_DATA_FIELD_NAME_MAP.get(returnField), "$statsData.", statsTimeRangeSince, statsTimeRangeUntil));
                    groupChildObject3.put(returnField, new BasicDBObject("$sum", "$" + returnField));
                    adsObject.put(returnField, "$" + returnField);
                    groupChildObject4.put(returnField, new BasicDBObject("$sum", "$" + returnField));
                    projectChildObject2.put(returnField, "$" + returnField);
                    summaryObject.put(returnField, "$" + returnField);
                    break;
                case "gaCartR":
                    groupChildObject2.put("gaCart", createSumObjectByTimeRange(GA_DATA_FIELD_NAME_MAP.get("gaCart"), "$statsData.", statsTimeRangeSince, statsTimeRangeUntil));
                    groupChildObject2.put("gaClicks", createSumObjectByTimeRange(GA_DATA_FIELD_NAME_MAP.get("gaClicks"), "$statsData.", statsTimeRangeSince, statsTimeRangeUntil));
                    groupChildObject3.put("gaCart", new BasicDBObject("$sum", "$gaCart"));
                    groupChildObject3.put("gaClicks", new BasicDBObject("$sum", "$gaClicks"));
                    projectChildObject2.put("gaCart", "$gaCart");
                    projectChildObject2.put("gaClicks", "$gaClicks");
                    groupChildObject4.put("gaCart", new BasicDBObject("$sum", "$gaCart"));
                    groupChildObject4.put("gaClicks", new BasicDBObject("$sum", "$gaClicks"));
                    adsObject.put("gaCartR", createDivideObject("$gaCart", "$gaClicks", 0L));
                    projectChildObject2.put("gaCartR", createDivideObject("$gaCart", "$gaClicks", 0L));
                    summaryObject.put("gaCartR", createDivideObject("$gaCart", "$gaClicks", 0L));
                    break;
                case "gaCpa":
                    groupChildObject2.put("gaSpent", createSumObjectByTimeRange("spent", "$statsData.", statsTimeRangeSince, statsTimeRangeUntil));
                    groupChildObject2.put("gaPays", createSumObjectByTimeRange(GA_DATA_FIELD_NAME_MAP.get("gaPays"), "$statsData.", statsTimeRangeSince, statsTimeRangeUntil));
                    groupChildObject3.put("gaSpent", new BasicDBObject("$sum", "$gaSpent"));
                    groupChildObject3.put("gaPays", new BasicDBObject("$sum", "$gaPays"));
                    projectChildObject2.put("gaSpent", "$gaSpent");
                    projectChildObject2.put("gaPays", "$gaPays");
                    groupChildObject4.put("gaSpent", new BasicDBObject("$sum", "$gaSpent"));
                    groupChildObject4.put("gaPays", new BasicDBObject("$sum", "$gaPays"));
                    adsObject.put(returnField, createDivideObject("$gaSpent", "$gaPays", "$gaSpent"));
                    projectChildObject2.put(returnField, createDivideObject("$gaSpent", "$gaPays", "$gaSpent"));
                    summaryObject.put(returnField, createDivideObject("$gaSpent", "$gaPays", "$gaSpent"));
                    break;
                case "gaCpc":
                    groupChildObject2.put("gaSpent", createSumObjectByTimeRange("spent", "$statsData.", statsTimeRangeSince, statsTimeRangeUntil));
                    groupChildObject2.put("gaClicks", createSumObjectByTimeRange(GA_DATA_FIELD_NAME_MAP.get("gaClicks"), "$statsData.", statsTimeRangeSince, statsTimeRangeUntil));
                    groupChildObject3.put("gaSpent", new BasicDBObject("$sum", "$gaSpent"));
                    groupChildObject3.put("gaClicks", new BasicDBObject("$sum", "$gaClicks"));
                    projectChildObject2.put("gaSpent", "$gaSpent");
                    projectChildObject2.put("gaClicks", "$gaClicks");
                    groupChildObject4.put("gaSpent", new BasicDBObject("$sum", "$gaSpent"));
                    groupChildObject4.put("gaClicks", new BasicDBObject("$sum", "$gaClicks"));
                    adsObject.put(returnField, createDivideObject("$gaSpent", "$gaClicks", "$gaSpent"));
                    projectChildObject2.put(returnField, createDivideObject("$gaSpent", "$gaClicks", "$gaSpent"));
                    summaryObject.put(returnField, createDivideObject("$gaSpent", "$gaClicks", "$gaSpent"));
                    break;
                case "gaOrderPrice":
                    groupChildObject2.put("gaOrderAmount", createSumObjectByTimeRange(GA_DATA_FIELD_NAME_MAP.get("gaOrderAmount"), "$statsData.", statsTimeRangeSince, statsTimeRangeUntil));
                    groupChildObject2.put("gaPays", createSumObjectByTimeRange(GA_DATA_FIELD_NAME_MAP.get("gaPays"), "$statsData.", statsTimeRangeSince, statsTimeRangeUntil));
                    groupChildObject3.put("gaOrderAmount", new BasicDBObject("$sum", "$gaOrderAmount"));
                    groupChildObject3.put("gaPays", new BasicDBObject("$sum", "$gaPays"));
                    projectChildObject2.put("gaOrderAmount", "$gaOrderAmount");
                    projectChildObject2.put("gaPays", "$gaPays");
                    groupChildObject4.put("gaOrderAmount", new BasicDBObject("$sum", "$gaOrderAmount"));
                    groupChildObject4.put("gaPays", new BasicDBObject("$sum", "$gaPays"));
                    adsObject.put(returnField, createDivideObject("$gaOrderAmount", "$gaPays", "$gaOrderAmount"));
                    projectChildObject2.put(returnField, createDivideObject("$gaOrderAmount", "$gaPays", "$gaOrderAmount"));
                    summaryObject.put(returnField, createDivideObject("$gaOrderAmount", "$gaPays", "$gaOrderAmount"));
                    break;
                case "gaRoi":
                    groupChildObject2.put("gaOrderAmount", createSumObjectByTimeRange(GA_DATA_FIELD_NAME_MAP.get("gaOrderAmount"), "$statsData.", statsTimeRangeSince, statsTimeRangeUntil));
                    groupChildObject2.put("gaSpent", createSumObjectByTimeRange("spent", "$statsData.", statsTimeRangeSince, statsTimeRangeUntil));
                    groupChildObject3.put("gaOrderAmount", new BasicDBObject("$sum", "$gaOrderAmount"));
                    groupChildObject3.put("gaSpent", new BasicDBObject("$sum", "$gaSpent"));
                    projectChildObject2.put("gaOrderAmount", "$gaOrderAmount");
                    projectChildObject2.put("gaSpent", "$gaSpent");
                    groupChildObject4.put("gaOrderAmount", new BasicDBObject("$sum", "$gaOrderAmount"));
                    groupChildObject4.put("gaSpent", new BasicDBObject("$sum", "$gaSpent"));
                    adsObject.put(returnField, createDivideObject("$gaOrderAmount", "$gaSpent", 0L));
                    projectChildObject2.put(returnField, createDivideObject("$gaOrderAmount", "$gaSpent", 0L));
                    summaryObject.put(returnField, createDivideObject("$gaOrderAmount", "$gaSpent", 0L));
                    break;
                case "gaCr":
                    groupChildObject2.put("gaPays", createSumObjectByTimeRange(GA_DATA_FIELD_NAME_MAP.get("gaPays"), "$statsData.", statsTimeRangeSince, statsTimeRangeUntil));
                    groupChildObject2.put("gaClicks", createSumObjectByTimeRange(GA_DATA_FIELD_NAME_MAP.get("gaClicks"), "$statsData.", statsTimeRangeSince, statsTimeRangeUntil));
                    groupChildObject3.put("gaPays", new BasicDBObject("$sum", "$gaPays"));
                    groupChildObject3.put("gaClicks", new BasicDBObject("$sum", "$gaClicks"));
                    projectChildObject2.put("gaPays", "$gaPays");
                    projectChildObject2.put("gaClicks", "$gaClicks");
                    groupChildObject4.put("gaPays", new BasicDBObject("$sum", "$gaPays"));
                    groupChildObject4.put("gaClicks", new BasicDBObject("$sum", "$gaClicks"));
                    adsObject.put(returnField, createDivideObject("$gaPays", "$gaClicks", 0L));
                    projectChildObject2.put(returnField, createDivideObject("$gaPays", "$gaClicks", 0L));
                    summaryObject.put(returnField, createDivideObject("$gaPays", "$gaClicks", 0L));
                    break;
                default:
                    if (returnField.startsWith("today")) {
                        // 去除today
                        String todayRemovedField = returnField.replace("today", "");
                        groupChildObject2.put(returnField, createSumObjectByTimeRange(todayRemovedField.replace(todayRemovedField.substring(0, 1), todayRemovedField.substring(0, 1).toLowerCase()), "$statsData.", statsToday, statsToday));
                    } else {
                        groupChildObject2.put(returnField, createSumObjectByTimeRange(returnField, "$statsData.", statsTimeRangeSince, statsTimeRangeUntil));
                    }
                    groupChildObject3.put(returnField, new BasicDBObject("$sum", "$" + returnField));
                    adsObject.put(returnField, "$" + returnField);
                    groupChildObject4.put(returnField, new BasicDBObject("$sum", "$" + returnField));
                    projectChildObject2.put(returnField, "$" + returnField);
                    summaryObject.put(returnField, "$" + returnField);
                    break;
            }
        }

        // 组合步骤，总共12步
        List<DBObject> aggregateObjects = new ArrayList<>();
        // 第1步，match，根据页面传过来的参数过滤数据
        aggregateObjects.add(new BasicDBObject("$match", matchChildObject1));
        // 第2步，group，根据adSetId分组，以便减少Lookup的数据量
        aggregateObjects.add(new BasicDBObject("$group", groupChildObject1));
        // 第3步，lookup，拼接统计数据
        aggregateObjects.add(new BasicDBObject("$lookup", lookupChildObject));
        // 第4步，unwind，将之前通过adSet聚合到一起的广告分割开
        aggregateObjects.add(new BasicDBObject("$unwind", unwindChild1Object1));
        // 第5步，project，过滤统计数据，只保留统计周期和今日的数据
        aggregateObjects.add(new BasicDBObject("$project", projectChildObject1));
        // 第6步，unwind，将统计数据分割开来，然后再进行SUM等计算
        aggregateObjects.add(new BasicDBObject("$unwind", unwindChild1Object2));
        // 第7步，group，根据ad分组，计算ad的统计数据
        aggregateObjects.add(new BasicDBObject("$group", groupChildObject2));
        // 第8步，group，根据adSet分组，计算adSet的统计数据，并且将ad的数据放到一个数组里
        aggregateObjects.add(new BasicDBObject("$group", groupChildObject3));
        // 第9步，project，计算adSet的ctr,cr,cost等
        aggregateObjects.add(new BasicDBObject("$project", projectChildObject2));
        // 第10步，sort，根据指定的排序条件进行排序，另外为了避免每次查询得到的数据不一样，第二排序因子默认采用asSetId
        aggregateObjects.add(new BasicDBObject("$sort", sortChildObject1));
        // 第11步，group，将所有数据汇总到一起，计算统计行和总条数等
        aggregateObjects.add(new BasicDBObject("$group", groupChildObject4));
        // 第12步，project，分页，计算汇总数据的ctr,cr,cost等
        aggregateObjects.add(new BasicDBObject("$project", projectChildObject3));
        logger.info("Mongo查询语句:" + com.mongodb.util.JSON.serialize(aggregateObjects));

        long start = System.currentTimeMillis();
        AggregationOptions aggregationOptions = AggregationOptions.builder().allowDiskUse(true).build();
        Cursor cursor = mongoDBClient.getCollection(MongoConstants.TABLE_NAME_ADGROUP).aggregate(aggregateObjects, aggregationOptions);
        logger.info("Mongo聚合查询花费时间：" + (System.currentTimeMillis() - start) + "毫秒");

        Map<String, Object> result = null;
        try {
            DBObject dbObject = null;
            if (cursor.hasNext()) {
                dbObject = cursor.next();
                result = JSONObject.parseObject(JSON.toJSONString(dbObject), Map.class);
            }
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        } finally {
            cursor.close();
        }
        return result;
    }

    /**
     * 创建除法对象
     *
     * @param dividedField 被除数字段
     * @param divideObject 除数字段
     * @param defaultField 除数为0时默认返回的结果字段
     * @return 创建好的对象
     */
    private BasicDBObject createDivideObject(String dividedField, String divideObject, String defaultField) {
        return new BasicDBObject("$cond",
                new BasicDBObject()
                        .append("if", new BasicDBObject("$eq", new Object[]{divideObject, 0}))
                        .append("then", defaultField)
                        .append("else", new BasicDBObject("$divide", new String[]{dividedField, divideObject})));
    }

    /**
     * 创建除法对象
     *
     * @param dividedField 被除数字段
     * @param divideObject 除数字段
     * @param defaultValue 除数为0时默认返回的值
     * @return 创建好的对象
     */
    private BasicDBObject createDivideObject(String dividedField, String divideObject, Long defaultValue) {
        return new BasicDBObject("$cond",
                new BasicDBObject()
                        .append("if", new BasicDBObject("$eq", new Object[]{divideObject, 0}))
                        .append("then", defaultValue)
                        .append("else", new BasicDBObject("$divide", new String[]{dividedField, divideObject})));
    }

    /**
     * 创建指定field在统计周期statsTimeRangeSince和statsTimeRangeUntil之间的SUM数据
     *
     * @param field               指定field
     * @param parentField         由于有些时候field不是在第一层，因此可以通过制定parentField来指定它的父级
     * @param statsTimeRangeSince 统计周期开始日
     * @param statsTimeRangeUntil 统计周期结束日
     * @return 创建好的对象
     */
    private BasicDBObject createSumObjectByTimeRange(String field, String parentField, String statsTimeRangeSince, String statsTimeRangeUntil) {
        // 容错处理
        if (null == parentField) {
            parentField = "";
        }
        BasicDBObject basicDBObject = new BasicDBObject("$sum",
                new BasicDBObject("$cond",
                        new BasicDBObject()
                                .append("if", new BasicDBObject("$and", new BasicDBObject[]{
                                        new BasicDBObject("$gte", new String[]{parentField + "day", statsTimeRangeSince}),
                                        new BasicDBObject("$lte", new String[]{parentField + "day", statsTimeRangeUntil})
                                }))
                                .append("then", parentField + field)
                                .append("else", 0)));
        return basicDBObject;
    }

    /**
     * 创建针对同一个字段进行正则表达式匹配的多个过滤条件列表，比如正则匹配name为A并且name为B
     *
     * @param matchList
     * @return 创建
     */
    private List<BasicDBObject> createRegexChildList(String matchField, List<String> matchList) {
        if (null == matchList) {
            return null;
        }
        List<BasicDBObject> andMatchList = new ArrayList<>();
        String matchValue = null;
        for (String adName : matchList) {
            matchValue = convertBlankToRegexExpression(adName);
            andMatchList.add(new BasicDBObject(matchField, new BasicDBObject().append("$regex", ".*" + matchValue + ".*").append("$options", "i")));
        }
        return andMatchList;
    }

    /**
     * 转换字符串中的空格为正则表达式形式表现，比如a b  c会转换成a[\s]{1}b[\s]{2}c
     *
     * @param orgStr
     * @return
     */
    private String convertBlankToRegexExpression(String orgStr) {
        String targetStr = orgStr;
        try {
            Pattern pattern = Pattern.compile("[\\s]+");
            Matcher matcher = pattern.matcher(targetStr);
            while (matcher.find()) {
                String group = matcher.group();
                targetStr = targetStr.replaceFirst(group, "[\\\\s]{" + group.length() + "}");
            }
        } catch (Exception e) {
            logger.error("字符转换失败，原始字符串：" + orgStr, e);
        }
        return targetStr;
    }

    /**
     * 创建lookup对象
     *
     * @param from         源表即坐标
     * @param localField   源表字段
     * @param foreignField 关联表字段
     * @param as           存储字段名
     * @return 创建的lookup对象
     */
    private BasicDBObject createLookupProject(String from, String localField, String foreignField, String as) {
        // lookup，拼接业务数据
        return new BasicDBObject()
                .append("from", from)
                .append("localField", localField)
                .append("foreignField", foreignField)
                .append("as", as);
    }

    @Override
    public AdSetForMongo findAdSetForMongo(Long adSet_id) {
        DBObject match = new BasicDBObject("adset_id", adSet_id);
        DBObject group = new BasicDBObject("_id", "$adset_id").append("adSet", new BasicDBObject("$first", "$adSet"))
                .append("campaign", new BasicDBObject("$first", "$campaign"));
        DBObject lookup = new BasicDBObject("from", "adgroup").append("localField", "_id")
                .append("foreignField", "adset_id").append("as", "ads");


        // 组合步骤，总共11步
        List<DBObject> aggregateObjects = new ArrayList<>();
        // 第1步，match，根据页面传过来的参数过滤数据
        aggregateObjects.add(new BasicDBObject("$match", match));
        // 第2步，group，根据adSetId分组，以便减少Lookup的数据量
        aggregateObjects.add(new BasicDBObject("$group", group));
        // 第3步，project，分页，计算ctr,cr,cost等
        aggregateObjects.add(new BasicDBObject("$lookup", lookup));
        logger.info("Mongo查询语句:" + com.mongodb.util.JSON.serialize(aggregateObjects));

        long start = System.currentTimeMillis();
        AggregationOptions aggregationOptions = AggregationOptions.builder().allowDiskUse(true).build();
        Cursor cursor = mongoDBClient.getCollection(MongoConstants.TABLE_NAME_ADGROUP).aggregate(aggregateObjects, aggregationOptions);
        logger.info("Mongo聚合查询花费时间：" + (System.currentTimeMillis() - start) + "毫秒");
        try {
            DBObject dbObject = null;
            if (cursor.hasNext()) {
                dbObject = cursor.next();
                Object ads = dbObject.get("ads");
                Object campaign = dbObject.get("campaign");
                dbObject = (DBObject) dbObject.get("adSet");
                dbObject.put("ads", ads);
                dbObject.put("campaign", campaign);
                return JSONObject.parseObject(JSONObject.toJSONString(dbObject), AdSetForMongo.class);
            }
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        } finally {
            cursor.close();
        }
        return null;
    }

    @Override
    public void updateAdSetForMongo(Long adSet_id, Map<String, Object> map) {
        Long start = System.currentTimeMillis();
        DBCollection dbCollection = mongoDBClient.getCollection(MongoConstants.TABLE_NAME_ADGROUP);
        DBObject update = new BasicDBObject();
        update.putAll(map);
        DBObject updateData = new BasicDBObject();
        updateData.put("$set", update);
        Date dateUpdateTime = new Date();
        update.put("dataUpdateTime", dateUpdateTime);
        dbCollection.update(new BasicDBObject("adset_id", adSet_id), updateData, true, true, WriteConcern.JOURNALED);
        logger.info("更新ADSET成功,广告对象的mongoid：{},花费时间为{}", adSet_id, System.currentTimeMillis() - start);
    }

    @Override
    public List<Map<String, Object>> groupByDay(GroupByDayParam groupByDayParam) {
        // match，根据页面传过来的参数过滤数据
        TimeRange statsTimeRange = groupByDayParam.getStatsTimeRange();
        BasicDBObject matchChildObject1 = new BasicDBObject()
                .append("breakdowns", "all")                            // 细分字段过滤
                .append("day", new BasicDBObject()
                        .append("$gte", statsTimeRange.getSince())
                        .append("$lte", statsTimeRange.getUntil()))     // 统计周期过滤
                .append("$or", new BasicDBObject[]{
                        new BasicDBObject("adGroupId", groupByDayParam.getId()),
                        new BasicDBObject("campaignId", groupByDayParam.getId()),
                        new BasicDBObject("accountId", groupByDayParam.getId())
                });                                                     // 统计对象过滤，广告Id，广告组Id，账号Id三选一

        // group，根据adSetId分组，以便减少Lookup的数据量
        BasicDBObject groupChildObject1 = new BasicDBObject()
                .append("_id", "$day");                                 // 分组条件

        // project，计算ctr,cr,cost等
        BasicDBObject projectChildObject1 = new BasicDBObject()
                .append("day", "$_id");
        List<String> returnStatsFields = groupByDayParam.getReturnStatsFields();
        // 对于需要返回的统计字段，进行统一处理
        String resultsField = "results";
        String resultsMapField = "results";
        if (null != groupByDayParam.getEcReport() && groupByDayParam.getEcReport()) {  // EC报表时转化应该对应purchase
            resultsField = "purchase";
            resultsMapField = "convertedArray.offsite_conversion.fb_pixel_purchase.actions.1d_click";
        }
        for (String returnField : returnStatsFields) {
            switch (returnField) {
                case "ctr":
                    groupChildObject1.put("impressions", new BasicDBObject("$sum", "$impressions"));
                    groupChildObject1.put("clicks", new BasicDBObject("$sum", "$clicks"));
                    projectChildObject1.put(returnField, createDivideObject("$clicks", "$impressions", 0L));
                    break;
                case "cr":
                    groupChildObject1.put("clicks", new BasicDBObject("$sum", "$clicks"));
                    groupChildObject1.put(resultsField, new BasicDBObject("$sum", "$" + resultsMapField));
                    projectChildObject1.put(returnField, createDivideObject("$" + resultsField, "$clicks", 0L));
                    break;
                case "roi":
                    groupChildObject1.put("salesAmount", new BasicDBObject("$sum", "$convertedArray.offsite_conversion.fb_pixel_purchase.actionValues.1d_click"));
                    groupChildObject1.put("spent", new BasicDBObject("$sum", "$spent"));
                    projectChildObject1.put(returnField, createDivideObject("$salesAmount", "$spent", 0L));
                    break;
                case "cpc":
                    groupChildObject1.put("spent", new BasicDBObject("$sum", "$spent"));
                    groupChildObject1.put("clicks", new BasicDBObject("$sum", "$clicks"));
                    projectChildObject1.put(returnField, createDivideObject("$spent", "$clicks", "$spent"));
                    break;
                case "cost":
                case "cps":
                    groupChildObject1.put("spent", new BasicDBObject("$sum", "$spent"));
                    groupChildObject1.put(resultsField, new BasicDBObject("$sum", "$" + resultsMapField));
                    projectChildObject1.put(returnField, createDivideObject("$spent", "$" + resultsField, "$spent"));
                    break;
                case "retr":
                    projectChildObject1.put("retr", new BasicDBObject("$literal", "-"));
                    break;
                case "cart":
                    groupChildObject1.put(returnField, new BasicDBObject("$sum", "$convertedArray.offsite_conversion.fb_pixel_add_to_cart.actions.1d_click"));
                    projectChildObject1.put(returnField, 1);
                    break;
                case "purchase":
                    groupChildObject1.put(returnField, new BasicDBObject("$sum", "$convertedArray.offsite_conversion.fb_pixel_purchase.actions.1d_click"));
                    projectChildObject1.put(returnField, 1);
                    break;
                case "salesAmount":
                    groupChildObject1.put(returnField, new BasicDBObject("$sum", "$convertedArray.offsite_conversion.fb_pixel_purchase.actionValues.1d_click"));
                    projectChildObject1.put(returnField, 1);
                    break;
                case "cartR":
                    groupChildObject1.put("cart", new BasicDBObject("$sum", "$convertedArray.offsite_conversion.fb_pixel_add_to_cart.actions.1d_click"));
                    groupChildObject1.put("clicks", new BasicDBObject("$sum", "$clicks"));
                    projectChildObject1.put(returnField, createDivideObject("$cart", "$clicks", 0L));
                    break;
                case "purchaseR":
                    groupChildObject1.put("purchase", new BasicDBObject("$sum", "$convertedArray.offsite_conversion.fb_pixel_purchase.actions.1d_click"));
                    groupChildObject1.put("cart", new BasicDBObject("$sum", "$convertedArray.offsite_conversion.fb_pixel_add_to_cart.actions.1d_click"));
                    projectChildObject1.put(returnField, createDivideObject("$purchase", "$cart", 0L));
                    break;
                case "gaClicks":
                case "gaCart":
                case "gaPays":
                case "gaOrderAmount":
                    groupChildObject1.put(returnField, new BasicDBObject("$sum", "$" + GA_DATA_FIELD_NAME_MAP.get(returnField)));
                    projectChildObject1.put(returnField, 1);
                    break;
                case "gaCartR":
                    groupChildObject1.put("gaCart", new BasicDBObject("$sum", "$" + GA_DATA_FIELD_NAME_MAP.get("gaCart")));
                    groupChildObject1.put("gaClicks", new BasicDBObject("$sum", "$" + GA_DATA_FIELD_NAME_MAP.get("gaClicks")));
                    projectChildObject1.put("gaCartR", createDivideObject("$gaCart", "$gaClicks", 0L));
                    break;
                case "gaCpa":
                    groupChildObject1.put("gaSpent", new BasicDBObject("$sum", "$spent"));
                    groupChildObject1.put("gaPays", new BasicDBObject("$sum", "$" + GA_DATA_FIELD_NAME_MAP.get("gaPays")));
                    projectChildObject1.put(returnField, createDivideObject("$gaSpent", "$gaPays", "$gaSpent"));
                    break;
                case "gaCpc":
                    groupChildObject1.put("gaSpent", new BasicDBObject("$sum", "$spent"));
                    groupChildObject1.put("gaClicks", new BasicDBObject("$sum", "$" + GA_DATA_FIELD_NAME_MAP.get("gaClicks")));
                    projectChildObject1.put(returnField, createDivideObject("$gaSpent", "$gaClicks", "$gaSpent"));
                    break;
                case "gaOrderPrice":
                    groupChildObject1.put("gaOrderAmount", new BasicDBObject("$sum", "$" + GA_DATA_FIELD_NAME_MAP.get("gaOrderAmount")));
                    groupChildObject1.put("gaPays", new BasicDBObject("$sum", "$" + GA_DATA_FIELD_NAME_MAP.get("gaPays")));
                    projectChildObject1.put(returnField, createDivideObject("$gaOrderAmount", "$gaPays", "$gaOrderAmount"));
                    break;
                case "gaRoi":
                    groupChildObject1.put("gaOrderAmount", new BasicDBObject("$sum", "$" + GA_DATA_FIELD_NAME_MAP.get("gaOrderAmount")));
                    groupChildObject1.put("gaSpent", new BasicDBObject("$sum", "$spent"));
                    projectChildObject1.put(returnField, createDivideObject("$gaOrderAmount", "$gaSpent", 0L));
                    break;
                case "gaCr":
                    groupChildObject1.put("gaPays", new BasicDBObject("$sum", "$" + GA_DATA_FIELD_NAME_MAP.get("gaPays")));
                    groupChildObject1.put("gaClicks", new BasicDBObject("$sum", "$" + GA_DATA_FIELD_NAME_MAP.get("gaClicks")));
                    projectChildObject1.put(returnField, createDivideObject("$gaPays", "$gaClicks", 0L));
                    break;
                default:
                    groupChildObject1.put(returnField, new BasicDBObject("$sum", "$" + returnField));
                    projectChildObject1.put(returnField, 1);
                    break;
            }
        }

        // 组合步骤，总共11步
        List<DBObject> aggregateObjects = new ArrayList<>();
        // 第1步，match，根据页面传过来的参数过滤数据
        aggregateObjects.add(new BasicDBObject("$match", matchChildObject1));
        // 第2步，group，根据adSetId分组，以便减少Lookup的数据量
        aggregateObjects.add(new BasicDBObject("$group", groupChildObject1));
        // 第3步，project，分页，计算ctr,cr,cost等
        aggregateObjects.add(new BasicDBObject("$project", projectChildObject1));
        logger.info("Mongo查询语句:" + com.mongodb.util.JSON.serialize(aggregateObjects));

        long start = System.currentTimeMillis();
        AggregationOptions aggregationOptions = AggregationOptions.builder().allowDiskUse(true).build();
        Cursor cursor = mongoDBClient.getCollection(MongoConstants.TABLE_NAME_ADGROUP_STATS_BY_DAY_90DAYS).aggregate(aggregateObjects, aggregationOptions);
        logger.info("Mongo聚合查询花费时间：" + (System.currentTimeMillis() - start) + "毫秒");

        List<Map<String, Object>> result = new ArrayList<>();
        try {
            DBObject dbObject = null;
            while (cursor.hasNext()) {
                dbObject = cursor.next();
                result.add(JSONObject.parseObject(JSON.toJSONString(dbObject), Map.class));
            }
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        } finally {
            cursor.close();
        }
        return result;
    }

    @Override
    public AdSetForMongo findAdSetWithAdsByAdSetId(Long adSetId) {
        // match，根据adset_id过滤数据
        BasicDBObject matchChildObject1 = new BasicDBObject("adset_id", adSetId);
        // group，分组，将ad放到一个数组中
        BasicDBObject groupChildObject1 = new BasicDBObject()
                .append("_id", "$adset_id")
                .append("ads", new BasicDBObject("$push", "$$CURRENT"))
                .append("adSet", new BasicDBObject("$first", "$adSet"))
                .append("campaign", new BasicDBObject("$first", "$campaign"));
        // 组合步骤，总共2步
        List<DBObject> aggregateObjects = new ArrayList<>();
        // 第1步，match，根据adset_id过滤数据
        aggregateObjects.add(new BasicDBObject("$match", matchChildObject1));
        // 第2步，group，分组，将ad放到一个数组中
        aggregateObjects.add(new BasicDBObject("$group", groupChildObject1));
        logger.info("Mongo查询语句:" + com.mongodb.util.JSON.serialize(aggregateObjects));

        long start = System.currentTimeMillis();
        AggregationOptions aggregationOptions = AggregationOptions.builder().allowDiskUse(true).build();
        Cursor cursor = mongoDBClient.getCollection(MongoConstants.TABLE_NAME_ADGROUP).aggregate(aggregateObjects, aggregationOptions);
        logger.info("Mongo聚合查询花费时间：" + (System.currentTimeMillis() - start) + "毫秒");

        AdSetForMongo result = null;
        try {
            if (cursor.hasNext()) {
                DBObject dbObject = cursor.next();
                List<BasicDBObject> ads = (List<BasicDBObject>) dbObject.get("ads");
                BasicDBObject adSet = (BasicDBObject) dbObject.get("adSet");
                adSet.put("campaign", dbObject.get("campaign"));
                // 删除ad中多余的字段
                ads.forEach(ad -> {
                    ad.removeField("adSet");
                    ad.removeField("campaign");
                });
                adSet.put("ads", ads);
                result = JSON.parseObject(JSON.toJSONString(adSet), AdSetForMongo.class);
            }
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        } finally {
            cursor.close();
        }
        return result;
    }

    @Override
    public List<Long> getNoDeletedCampaignId(Long account_id, int skip, int limit) {
        DBCollection dbCollection = mongoDBClient.getCollection(MongoConstants.TABLE_NAME_ADGROUP);

        BasicDBObject matchChildObject1 = new BasicDBObject()
                .append("campaign.configured_status", new BasicDBObject("$nin", new String[]{"DELETED", "ARCHIVED"}))
                .append("account_id", account_id);
        BasicDBObject groupChildObject1 = new BasicDBObject("_id", "$campaign_id");
        // 缁勫悎姝ラ锛屾�诲叡11姝�
        List<DBObject> aggregateObjects = new ArrayList<>();
        // 绗�1姝ワ紝match锛屾牴鎹〉闈紶杩囨潵鐨勫弬鏁拌繃婊ゆ暟鎹�
        aggregateObjects.add(new BasicDBObject("$match", matchChildObject1));
        // 绗�2姝ワ紝group锛屾牴鎹產dSetId鍒嗙粍锛屼互渚垮噺灏慙ookup鐨勬暟鎹噺
        aggregateObjects.add(new BasicDBObject("$group", groupChildObject1));
        aggregateObjects.add(new BasicDBObject("$skip", skip));
        aggregateObjects.add(new BasicDBObject("$limit", limit));
        long start = System.currentTimeMillis();
        AggregationOptions aggregationOptions = AggregationOptions.builder().allowDiskUse(true).build();
        Cursor cursor = dbCollection.aggregate(aggregateObjects, aggregationOptions);
        logger.info("Mongo聚合查询花费时间：" + (System.currentTimeMillis() - start) + "毫秒");
        List<Long> result = new ArrayList<>();
        try {
            DBObject dbObject = null;
            while (cursor.hasNext()) {
                dbObject = cursor.next();
                result.add(Long.parseLong(dbObject.get("_id").toString()));
            }
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        } finally {
            cursor.close();
        }
        return result;
    }

    @Override
    public Map<Long, Map<String, Integer>> getAdCountGroupByAccountIdStatus() {
        List<DBObject> pipeline = new ArrayList<>();
        DBObject group = new BasicDBObject("_id", new BasicDBObject("account_id", "$account_id").append("effective_status", "$effective_status"));
        group.put("count", new BasicDBObject("$sum", 1));
        pipeline.add(new BasicDBObject("$group", group));
        Map<Long, Map<String, Integer>> result = new HashMap<>();
        Map<String, Integer> map = null;
        long start = System.currentTimeMillis();
        AggregationOptions aggregationOptions = AggregationOptions.builder().allowDiskUse(true).build();
        Cursor cursor = mongoDBClient.getCollection(MongoConstants.TABLE_NAME_ADGROUP).aggregate(pipeline, aggregationOptions);
        logger.info("Mongo聚合查询花费时间：" + (System.currentTimeMillis() - start) + "毫秒");
        try {
            if (cursor.hasNext()) {
                DBObject dbObject = cursor.next();
                if (dbObject != null && dbObject.get("count") != null) {
                    Long accountId = (Long) ((DBObject) dbObject.get("_id")).get("accountId");
                    if (result.containsKey(accountId)) {
                        map = result.get(accountId);
                    } else {
                        map = new HashMap<>();
                        result.put(accountId, map);
                    }
                    map.put(((DBObject) dbObject.get("_id")).get("effective_status").toString(), Integer.parseInt(dbObject.get("count").toString()));
                }
            }
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        } finally {
            cursor.close();
        }
        return result;
    }

    @Override
    public List<AdForMongo> findAdByAdMarketTargetId(String adMarketTargetId, String... returnFields) {
        DBCollection dbCollection = mongoDBClient.getCollection(MongoConstants.TABLE_NAME_ADGROUP);
        // TODO 这里需要和Hunk约定字段名称
        DBObject match = new BasicDBObject()
                .append("adMarketTargetId", adMarketTargetId);
        DBObject project = new BasicDBObject();
        for (String returnField : returnFields) {
            project.put(returnField, 1);
        }
        List<AdForMongo> ads = new ArrayList<>();
        DBCursor dbCursor = null;
        try {
            dbCursor = dbCollection.find(match, project);
            if (dbCursor == null) {
                return null;
            }
            while (dbCursor.hasNext()) {
                ads.add(JSONObject.parseObject(JSONObject.toJSONString(dbCursor.next()), AdForMongo.class));
            }
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        } finally {
            if (null != dbCursor) {
                dbCursor.close();
            }
        }
        return ads;
    }

    @Override
    public JSONObject groupByAdForAdMarketTarget(JSONObject param) {
        // match，根据页面传过来的参数过滤数据
        BasicDBObject matchChildObject1 = new BasicDBObject()
                .append("adSet.adset_id", new BasicDBObject("$ne", null));
        // 营销目标编号过滤
        String adMarketTargetId = param.getString("adMarketTargetId");
        if (StringUtils.isNotEmpty(adMarketTargetId)) {
            matchChildObject1.put("adMarketTargetId", adMarketTargetId);
        }
        // 广告编号过滤
        JSONArray adIds = param.getJSONArray("adIds");
        if (!CollectionUtils.isEmpty(adIds)) {
            matchChildObject1.put("ad_id", new BasicDBObject("$in", adIds));
        }

        // group，根据adSetId分组，以便减少Lookup的数据量
        BasicDBObject groupChildObject1 = new BasicDBObject()
                .append("_id", "$adset_id")                                                     // 分组条件
                .append("accountId", new BasicDBObject("$first", "$account_id"))                // 广告账号Id
                .append("adSetId", new BasicDBObject("$first", "$adset_id"))                    // 广告组编号
                .append("adSetName", new BasicDBObject("$first", "$adSet.name"))                // 广告组名称
                .append("adSetStatus", new BasicDBObject("$first", "$adSet.effective_status"))  // 广告组状态
                .append("adSetCreatedUtcTime", new BasicDBObject("$first", "$adSet.created_utc_time"))  // 广告组创建时间
                .append("adSetConfiguredStatus", new BasicDBObject("$first", "$adSet.configured_status"))  // 广告组状态
                .append("campaignName", new BasicDBObject("$first", "$campaign.name"))          // campaign名称
                .append("bidAmount", new BasicDBObject("$first", "$adSet.bid_amount"))          // 出价
                .append("budget", new BasicDBObject("$first",                                   // 预算
                        new BasicDBObject("$cond",
                                new BasicDBObject()
                                        .append("if", new BasicDBObject("$eq", new String[]{"$adSet.daily_budget", "0"}))
                                        .append("then", "$adSet.lifetime_budget")
                                        .append("else", "$adSet.daily_budget"))))
                .append("ads", new BasicDBObject("$push",
                        new BasicDBObject()
                                .append("adId", "$ad_id")                                       // 广告编号
                                .append("adName", "$name")                                      // 广告名称
                                .append("adStatus", "$effective_status")
                                .append("adCreatedUtcTime", "$created_utc_time")  // 广告创建时间
                                .append("adConfiguredStatus", "$configured_status")));                     // 广告状态

        // lookup，拼接统计数据
        BasicDBObject lookupChildObject = createLookupProject(MongoConstants.TABLE_NAME_ADGROUP_STATS_BY_DAY_90DAYS, "adSetId", "campaignId", "statsData");

        // unwind，将之前通过adSet聚合到一起的广告分割开
        String unwindChild1Object1 = "$ads";

        // project，过滤统计数据，只保留统计周期和今日的数据
        JSONObject statsTimeRange = param.getJSONObject("statsTimeRange");
        String statsTimeRangeSince = statsTimeRange.getString("since");
        String statsTimeRangeUntil = statsTimeRange.getString("until");
        String statsToday = param.getString("today");
        BasicDBObject projectChildObject1 = new BasicDBObject()
                .append("adId", "$ads.adId")
                .append("adStatus", "$ads.adStatus")
                .append("adCreatedUtcTime", "$ads.adCreatedUtcTime")
                .append("adConfiguredStatus", "$ads.adConfiguredStatus")
                .append("adName", "$ads.adName")
                .append("accountId", 1)
                .append("adSetId", 1)
                .append("adSetStatus", 1)
                .append("adSetCreatedUtcTime", 1)
                .append("adSetConfiguredStatus", 1)
                .append("adSetName", 1)
                .append("bidAmount", 1)
                .append("budget", 1)
                .append("campaignName", 1)
                .append("statsData", new BasicDBObject("$filter",
                        new BasicDBObject()
                                .append("input", "$statsData")
                                .append("as", "statsData")
                                .append("cond", new BasicDBObject()
                                        .append("$and", new BasicDBObject[]{
                                                new BasicDBObject("$eq", new String[]{"$ads.adId", "$$statsData.adGroupId"}),
                                                new BasicDBObject("$or", new BasicDBObject[]{
                                                        new BasicDBObject("$and", new BasicDBObject[]{
                                                                new BasicDBObject("$gte", new String[]{"$$statsData.day", statsTimeRangeSince}),
                                                                new BasicDBObject("$lte", new String[]{"$$statsData.day", statsTimeRangeUntil}),
                                                        }),
                                                        new BasicDBObject("$eq", new String[]{"$$statsData.day", statsToday})
                                                })
                                        }))));

        // unwind，将统计数据分割开来，然后再进行SUM等计算
        BasicDBObject unwindChild1Object2 = new BasicDBObject()
                .append("path", "$statsData")
                .append("preserveNullAndEmptyArrays", true);

        // group，根据ad分组，计算ad的统计数据
        BasicDBObject groupChildObject2 = new BasicDBObject()
                .append("_id", "$adId")
                .append("adId", new BasicDBObject("$first", "$adId"))
                .append("accountId", new BasicDBObject("$first", "$accountId"))
                .append("adStatus", new BasicDBObject("$first", "$adStatus"))
                .append("adConfiguredStatus", new BasicDBObject("$first", "$adConfiguredStatus"))
                .append("adCreatedUtcTime", new BasicDBObject("$first", "$adCreatedUtcTime"))
                .append("adName", new BasicDBObject("$first", "$adName"))
                .append("adSetId", new BasicDBObject("$first", "$adSetId"))
                .append("adSetStatus", new BasicDBObject("$first", "$adSetStatus"))
                .append("adSetCreatedUtcTime", new BasicDBObject("$first", "$adSetCreatedUtcTime"))  // 广告组创建时间
                .append("adSetConfiguredStatus", new BasicDBObject("$first", "$adSetConfiguredStatus"))
                .append("adSetName", new BasicDBObject("$first", "$adSetName"))
                .append("bidAmount", new BasicDBObject("$first", "$bidAmount"))
                .append("budget", new BasicDBObject("$first", "$budget"))
                .append("campaignName", new BasicDBObject("$first", "$campaignName"))
                .append("todaySpent", createSumObjectByTimeRange("spent", "$statsData.", statsToday, statsToday));  // 今日花费
        List returnStatsFields = param.getJSONArray("returnStatsFields");

        // project，计算adSet的ctr,cr,cost等
        BasicDBObject projectChildObject2 = new BasicDBObject()
                .append("_id", 0)
                .append("adId", 1)
                .append("accountId", 1)
                .append("adStatus", 1)
                .append("adConfiguredStatus", 1)
                .append("adCreatedUtcTime", 1)
                .append("adName", 1)
                .append("adSetId", 1)
                .append("adSetStatus", 1)
                .append("adSetCreatedUtcTime", 1)
                .append("adSetConfiguredStatus", 1)
                .append("adSetName", 1)
                .append("bidAmount", 1)
                .append("budget", 1)
                .append("campaignName", 1)
                .append("todaySpent", 1);

        // sort，根据指定的排序条件进行排序，另外为了避免每次查询得到的数据不一样，第二排序因子默认采用asSetId
        JSONObject sort = param.getJSONObject("sort");
        BasicDBObject sortChildObject1 = new BasicDBObject()
                .append(sort.getString("field"), sort.getInteger("type"))
                .append("createdUtcTime", 1);

        // group，将所有数据汇总到一起，计算统计行和总条数等
        BasicDBObject groupChildObject3 = new BasicDBObject()
                .append("_id", null)
                .append("adCount", new BasicDBObject("$sum", 1))                      // 广告数量
                .append("todaySpent", new BasicDBObject("$sum", "$todaySpent"))               // 今日花费
                .append("ads", new BasicDBObject("$push", "$$CURRENT"));                   // adSet数组

        // project，分页，计算ctr,cr,cost等
        Integer currentPage = param.getInteger("cPage");
        Integer pageSize = param.getInteger("pSize");
        int startNum = (currentPage - 1) * pageSize;
        BasicDBObject projectChildObject3 = new BasicDBObject()
                .append("ads", new BasicDBObject("$slice", new Object[]{"$ads", startNum, pageSize}));
        BasicDBObject summaryObject = new BasicDBObject()
                .append("adCount", "$adCount")
                .append("todaySpent", "$todaySpent");
        projectChildObject3.put("summary", summaryObject);

        // 对于需要返回的统计字段，进行统一处理
        String resultsField = "results";
        String resultsMapField = "results";
        Boolean isEcReport = param.getBoolean("ecReport");
        if (null != isEcReport && isEcReport) {  // EC报表时转化应该对应purchase
            resultsField = "purchase";
            resultsMapField = "convertedArray.offsite_conversion.fb_pixel_purchase.actions.1d_click";
        }
        for (int i = 0; i < returnStatsFields.size(); i++) {
            String returnField = returnStatsFields.get(i).toString();
            switch (returnField) {
                case "ctr":
                    groupChildObject2.put("impressions", createSumObjectByTimeRange("impressions", "$statsData.", statsTimeRangeSince, statsTimeRangeUntil));
                    groupChildObject2.put("clicks", createSumObjectByTimeRange("clicks", "$statsData.", statsTimeRangeSince, statsTimeRangeUntil));
                    projectChildObject2.put("impressions", "$impressions");
                    projectChildObject2.put("clicks", "$clicks");
                    groupChildObject3.put("impressions", new BasicDBObject("$sum", "$impressions"));
                    groupChildObject3.put("clicks", new BasicDBObject("$sum", "$clicks"));
                    projectChildObject2.put("ctr", createDivideObject("$clicks", "$impressions", 0L));
                    summaryObject.put("ctr", createDivideObject("$clicks", "$impressions", 0L));
                    break;
                case "cr":
                    groupChildObject2.put("clicks", createSumObjectByTimeRange("clicks", "$statsData.", statsTimeRangeSince, statsTimeRangeUntil));
                    groupChildObject2.put(resultsField, createSumObjectByTimeRange(resultsMapField, "$statsData.", statsTimeRangeSince, statsTimeRangeUntil));
                    projectChildObject2.put("clicks", "$clicks");
                    projectChildObject2.put(resultsField, "$" + resultsField);
                    groupChildObject3.put("clicks", new BasicDBObject("$sum", "$clicks"));
                    groupChildObject3.put(resultsField, new BasicDBObject("$sum", "$" + resultsField));
                    projectChildObject2.put("cr", createDivideObject("$" + resultsField, "$clicks", 0L));
                    summaryObject.put("cr", createDivideObject("$" + resultsField, "$clicks", 0L));
                    break;
                case "cpc":
                    groupChildObject2.put("spent", createSumObjectByTimeRange("spent", "$statsData.", statsTimeRangeSince, statsTimeRangeUntil));
                    groupChildObject2.put("clicks", createSumObjectByTimeRange("clicks", "$statsData.", statsTimeRangeSince, statsTimeRangeUntil));
                    projectChildObject2.put("spent", "$spent");
                    projectChildObject2.put("clicks", "$clicks");
                    groupChildObject3.put("spent", new BasicDBObject("$sum", "$spent"));
                    groupChildObject3.put("clicks", new BasicDBObject("$sum", "$clicks"));
                    projectChildObject2.put("cpc", createDivideObject("$spent", "$clicks", "$spent"));
                    summaryObject.put("cpc", createDivideObject("$spent", "$clicks", "$spent"));
                    break;
                case "cost":
                case "cps":
                    groupChildObject2.put("spent", createSumObjectByTimeRange("spent", "$statsData.", statsTimeRangeSince, statsTimeRangeUntil));
                    groupChildObject2.put(resultsField, createSumObjectByTimeRange(resultsMapField, "$statsData.", statsTimeRangeSince, statsTimeRangeUntil));
                    projectChildObject2.put("spent", "$spent");
                    projectChildObject2.put(resultsField, "$" + resultsField);
                    groupChildObject3.put("spent", new BasicDBObject("$sum", "$spent"));
                    groupChildObject3.put(resultsField, new BasicDBObject("$sum", "$" + resultsField));
                    projectChildObject2.put(returnField, createDivideObject("$spent", "$" + resultsField, "$spent"));
                    summaryObject.put(returnField, createDivideObject("$spent", "$" + resultsField, "$spent"));
                    break;
                case "purchaseR":
                    groupChildObject2.put("purchase", createSumObjectByTimeRange("convertedArray.offsite_conversion.fb_pixel_purchase.actions.1d_click", "$statsData.", statsTimeRangeSince, statsTimeRangeUntil));
                    groupChildObject2.put("cart", createSumObjectByTimeRange("convertedArray.offsite_conversion.fb_pixel_add_to_cart.actions.1d_click", "$statsData.", statsTimeRangeSince, statsTimeRangeUntil));
                    projectChildObject2.put("purchase", "$purchase");
                    projectChildObject2.put("cart", "$cart");
                    groupChildObject3.put("purchase", new BasicDBObject("$sum", "$purchase"));
                    groupChildObject3.put("cart", new BasicDBObject("$sum", "$purchase"));
                    projectChildObject2.put("purchaseR", createDivideObject("$purchase", "$cart", 0L));
                    summaryObject.put("purchaseR", createDivideObject("$purchase", "$cart", 0L));
                    break;
                case "roi":
                    groupChildObject2.put("salesAmount", createSumObjectByTimeRange("convertedArray.offsite_conversion.fb_pixel_purchase.actionValues.1d_click", "$statsData.", statsTimeRangeSince, statsTimeRangeUntil));
                    groupChildObject2.put("spent", createSumObjectByTimeRange("spent", "$statsData.", statsTimeRangeSince, statsTimeRangeUntil));
                    projectChildObject2.put("salesAmount", "$salesAmount");
                    projectChildObject2.put("spent", "$spent");
                    groupChildObject3.put("salesAmount", new BasicDBObject("$sum", "$salesAmount"));
                    groupChildObject3.put("spent", new BasicDBObject("$sum", "$spent"));
                    projectChildObject2.put("roi", createDivideObject("$salesAmount", "$spent", 0L));
                    summaryObject.put("roi", createDivideObject("$salesAmount", "$spent", 0L));
                    break;
                case "gaRoi":
                    groupChildObject2.put("gaTransactionRevenue", createSumObjectByTimeRange("gaTransactionRevenue", "$statsData.", statsTimeRangeSince, statsTimeRangeUntil));
                    groupChildObject2.put("spent", createSumObjectByTimeRange("spent", "$statsData.", statsTimeRangeSince, statsTimeRangeUntil));
                    projectChildObject2.put("gaTransactionRevenue", "$gaTransactionRevenue");
                    projectChildObject2.put("spent", "$spent");
                    groupChildObject3.put("gaTransactionRevenue", new BasicDBObject("$sum", "$gaTransactionRevenue"));
                    groupChildObject3.put("spent", new BasicDBObject("$sum", "$spent"));
                    projectChildObject2.put("gaRoi", createDivideObject("$gaTransactionRevenue", "$spent", 0L));
                    summaryObject.put("gaRoi", createDivideObject("$gaTransactionRevenue", "$spent", 0L));
                    break;
                case "retr":
                    projectChildObject2.put("retr", new BasicDBObject("$literal", "-"));
                    summaryObject.put("retr", new BasicDBObject("$literal", "-"));
                    break;
                case "cart":
                    groupChildObject2.put(returnField, createSumObjectByTimeRange("convertedArray.offsite_conversion.fb_pixel_add_to_cart.actions.1d_click", "$statsData.", statsTimeRangeSince, statsTimeRangeUntil));
                    groupChildObject3.put(returnField, new BasicDBObject("$sum", "$" + returnField));
                    projectChildObject2.put(returnField, "$" + returnField);
                    summaryObject.put(returnField, "$" + returnField);
                    break;
                case "purchase":
                    groupChildObject2.put(returnField, createSumObjectByTimeRange("convertedArray.offsite_conversion.fb_pixel_purchase.actions.1d_click", "$statsData.", statsTimeRangeSince, statsTimeRangeUntil));
                    groupChildObject3.put(returnField, new BasicDBObject("$sum", "$" + returnField));
                    projectChildObject2.put(returnField, "$" + returnField);
                    summaryObject.put(returnField, "$" + returnField);
                    break;
                case "salesAmount":
                    groupChildObject2.put(returnField, createSumObjectByTimeRange("convertedArray.offsite_conversion.fb_pixel_purchase.actionValues.1d_click", "$statsData.", statsTimeRangeSince, statsTimeRangeUntil));
                    groupChildObject3.put(returnField, new BasicDBObject("$sum", "$" + returnField));
                    projectChildObject2.put(returnField, "$" + returnField);
                    summaryObject.put(returnField, "$" + returnField);
                    break;
                default:
                    if (returnField.startsWith("today")) {
                        // 去除today
                        String todayRemovedField = returnField.replace("today", "");
                        groupChildObject2.put(returnField, createSumObjectByTimeRange(todayRemovedField.replace(todayRemovedField.substring(0, 1), todayRemovedField.substring(0, 1).toLowerCase()), "$statsData.", statsToday, statsToday));
                    } else {
                        groupChildObject2.put(returnField, createSumObjectByTimeRange(returnField, "$statsData.", statsTimeRangeSince, statsTimeRangeUntil));
                    }
                    groupChildObject3.put(returnField, new BasicDBObject("$sum", "$" + returnField));
                    projectChildObject2.put(returnField, "$" + returnField);
                    summaryObject.put(returnField, "$" + returnField);
                    break;
            }
        }

        // 组合步骤，总共12步
        List<DBObject> aggregateObjects = new ArrayList<>();
        // 第1步，match，根据页面传过来的参数过滤数据
        aggregateObjects.add(new BasicDBObject("$match", matchChildObject1));
        // 第2步，group，根据adSetId分组，以便减少Lookup的数据量
        aggregateObjects.add(new BasicDBObject("$group", groupChildObject1));
        // 第3步，lookup，拼接统计数据
        aggregateObjects.add(new BasicDBObject("$lookup", lookupChildObject));
        // 第4步，unwind，将之前通过adSet聚合到一起的广告分割开
        aggregateObjects.add(new BasicDBObject("$unwind", unwindChild1Object1));
        // 第5步，project，过滤统计数据，只保留统计周期和今日的数据
        aggregateObjects.add(new BasicDBObject("$project", projectChildObject1));
        // 第6步，unwind，将统计数据分割开来，然后再进行SUM等计算
        aggregateObjects.add(new BasicDBObject("$unwind", unwindChild1Object2));
        // 第7步，group，根据ad分组，计算ad的统计数据
        aggregateObjects.add(new BasicDBObject("$group", groupChildObject2));
        // 第8步，project，计算ad的ctr,cr,cost等
        aggregateObjects.add(new BasicDBObject("$project", projectChildObject2));
        // 第9步，sort，根据指定的排序条件进行排序，另外为了避免每次查询得到的数据不一样，第二排序因子默认采用asSetId
        aggregateObjects.add(new BasicDBObject("$sort", sortChildObject1));
        // 第10步，group，将所有数据汇总到一起，计算统计行和总条数等
        aggregateObjects.add(new BasicDBObject("$group", groupChildObject3));
        // 第11步，project，分页，计算汇总数据的ctr,cr,cost等
        aggregateObjects.add(new BasicDBObject("$project", projectChildObject3));
        logger.info("Mongo查询语句:" + com.mongodb.util.JSON.serialize(aggregateObjects));

        long start = System.currentTimeMillis();
        AggregationOptions aggregationOptions = AggregationOptions.builder().allowDiskUse(true).build();
        Cursor cursor = mongoDBClient.getCollection(MongoConstants.TABLE_NAME_ADGROUP).aggregate(aggregateObjects, aggregationOptions);
        logger.info("Mongo聚合查询花费时间：" + (System.currentTimeMillis() - start) + "毫秒");

        JSONObject result = new JSONObject();
        try {
            DBObject dbObject = null;
            if (cursor.hasNext()) {
                dbObject = cursor.next();
                result = JSONObject.parseObject(JSON.toJSONString(dbObject));
            }
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        } finally {
            cursor.close();
        }
        return result;
    }

    @Override
    public List<AdForMongo> findByNameIn(Set<String> adNames) {
        DBCollection dbCollection = mongoDBClient.getCollection(MongoConstants.TABLE_NAME_ADGROUP);
        BasicDBObject q = new BasicDBObject()
                .append("name", new BasicDBObject("$in", adNames));
        DBCursor dbCursor = dbCollection.find(q);
        List<AdForMongo> result = new ArrayList<>();
        if (dbCursor != null) {
            while (dbCursor.hasNext()) {
                result.add(JSONObject.parseObject(JSONObject.toJSONString(dbCursor.next()), AdForMongo.class));
            }
        }
        return result;

    }

    @Override
    public List<DBObject> findHasMaterieIdAndNoMaterieDetail() {
        DBCollection dbCollection = mongoDBClient.getCollection(MongoConstants.TABLE_NAME_ADGROUP);
        BasicDBObject basicDBObject = new BasicDBObject();
        basicDBObject.put("materie_id", new BasicDBObject("$exists", true));
        basicDBObject.put("materie_detail", new BasicDBObject("$exists", false));
        DBCursor dbCursor = dbCollection.find(basicDBObject);
        return dbCursor.toArray();
    }

    @Override
    public void update(DBObject query, DBObject update, boolean upsert, boolean multi) {
        DBObject updateData = new BasicDBObject();
        updateData.put("$set", update);
        DBCollection dbCollection = mongoDBClient.getCollection(MongoConstants.TABLE_NAME_ADGROUP);
        dbCollection.update(query, updateData, upsert, multi);
    }
}
